
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateAnimalProtocolStaffMaps]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateAnimalProtocolStaffMaps]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.UpdateAnimalProtocolStaffMaps
AS
BEGIN

	SET NOCOUNT ON

		
	-- ProtocolStaff_Map
	UPDATE ProtocolStaff_Map
	SET ProtocolStaffTypeId = Sub.ProtocolStaffingTypeId
		FROM (
		Select 	Staff.sFolderNumber,
				Staff.ProtocolStaffingTypeID,
				Staff.ProtocolStaffTypeId,
				Staff.AdmPersonId,
				Staff.dtModified,
				Staff.nAnimalProtId,
				Staff.nRecomAbstractId,
				Staff.StaffOrganizationId,
				Staff.ProtocolId
		FROM (Select Stg.sFolderNumber, Stg.ProtocolStaffingTypeID, M.ProtocolStaffTypeId, Stg.AdmPersonId, Stg.dtModified, 
			Stg.nAnimalProtId, Stg.nRecomAbstractId, Stg.StaffOrganizationId, Stg.ProtocolId  
		From Stg_AnimalStudyStaff Stg
		inner join ProtocolStaff_Map M
			on M.AdmPersonId = Stg.AdmPersonId
			and M.ProtocolNumber = Stg.sFolderNumber
			and M.IsCurrent = 1
			--and M.ProtocolStaffTypeId = Stg.ProtocolstaffingTypeId
			and M.ModuleType = 'A' 
		WHERE M.ProtocolStaffTypeID != Stg.ProtocolStaffingTypeId) Staff
		WHERE Staff.AdmPersonID Is not null ) SUB
	WHERE ModuleType = 'A'
		AND ProtocolStaff_Map.ProtocolNumber = Sub.sFolderNumber
		AND ProtocolStaff_Map.AdmPersonId = Sub.AdmPersonId
		AND ProtocolStaff_Map.IsCurrent = 1


	
	-- Find any not-PI staff maps who were deleted from Rex, and handle them.
	-- Note that these won't be in the staging table.
	UPDATE ProtocolStaff_Map SET IsCurrent = 0 
	WHERE ModuleType = 'A'	
	AND RemovedProtocolProcessID IS NULL
	AND 
	Id in (	SELECT M.Id
		FROM ProtocolStaff_Map M
		LEFT OUTER JOIN  Stg_AnimalStudyStaff Stg
			ON Stg.sFolderNumber = M.ProtocolNumber 
			AND Stg.AdmPersonId = M.AdmPersonId
			AND Stg.ProtocolStaffingTypeId = M.ProtocoLStaffTypeId
		WHERE M.ModuleType = 'A'
			AND M.IsCurrent = 1
			AND Stg.AdmPersonID IS NULL)
		AND ProtocolStaffTypeId !=13
		AND ProtocolNumber IS NOT NULL

	--Set to current re-activated staff members
	UPDATE ProtocolStaff_Map SET IsCurrent = 1 
	WHERE ModuleType = 'A'	
	AND RemovedProtocolProcessID IS NULL
	AND 
	Id in (	SELECT M.Id
		FROM ProtocolStaff_Map M
		JOIN  Stg_AnimalStudyStaff Stg
			ON Stg.sFolderNumber = M.ProtocolNumber 
			AND Stg.AdmPersonId = M.AdmPersonId
			AND Stg.ProtocolStaffingTypeId = M.ProtocoLStaffTypeId
		WHERE M.ModuleType = 'A'
			AND M.IsCurrent = 0
			AND Stg.AdmPersonID IS NOT NULL)
		AND ProtocoLStaffTypeId !=13

	-- Set records non-current for the old PI
	Update Map 
	set IsCurrent = 0 
	from ProtocolStaff_Map map
	join Protocol p
		on map.ProtocolNumber = p.ProtocolNumber
		and p.IsCurrent = 1
	where map.ModuleType = 'A' and p.ModuleType = 'A'
	and map.ProtocoLStaffTypeId = 13
	and map.AdmPersonId != p.PIPersonID


	-- Now handle the contact records to delete if they're 
	-- in the contact table, but no longer marked as a GetsMail person in Rex.
	DELETE FwkResource_AdmPerson_Contact_Map WHERE ID in (
	Select M.ID
	FROM FwkResource_AdmPerson_Contact_Map M
	INNER JOIN Protocol P
		on P.FwkResourceId = M.FwkResourceId
	INNER JOIN Stg_AnimalStudyStaff Stg
		on Stg.AdmPersonId = M.AdmPersonID
		AND Stg.ProtocolId = P.Id
	WHERE Stg.bGetsMail = 0)

	--Remove the deleted staff access to protocol completely
	DELETE FROM FwkResource_DomainUser_Map WHERE Id IN (
		SELECT FDMap.Id FROM FwkResource_DomainUser_Map FDMap
		INNER JOIN AdmPerson Adm ON Adm.FwkDomainUserId = FDMap.FwkDomainUserId
		INNER JOIN Protocol P ON P.FwkResourceId = FDMap.FwkResourceId
		INNER JOIN ProtocolStaff_Map M ON M.ProtocolNumber = P.ProtocolNumber AND M.AdmPersonId = Adm.Id
		WHERE M.ModuleType = 'A'
			AND M.IsCurrent = 0
			AND M.RemovedProtocolProcessID IS NULL)




	-- now insert any new Contact peoiple who were already in the database.
	insert into FwkResource_AdmPerson_Contact_Map ( AdmPersonId, FwkResourceId )
	Select	distinct(Staff.AdmPersonId), Prot.FwkResourceId
	FROM Stg_AnimalStudyStaff Staff
	inner join Protocol Prot
		ON Staff.ProtocolID = Prot.Id
	Where Prot.ModuleType = 'A' AND Staff.AdmPersonId is not null			
		and (Staff.bGetsMail = 1 OR Staff.ProtocolStaffingTypeID = 12)
		and not exists (select map.* from FwkResource_AdmPerson_Contact_Map Map
			Where Map.AdmPersonId = Staff.AdmPersonId and Map.FwkResourceId = Prot.FwkResourceId )
		and Prot.IsCurrent = 1

			
	SET NOCOUNT OFF

END



GO
